package com.example.dynamic_forms.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.example.dynamic_forms.client.Dynamic_forms;
import com.example.dynamic_forms.shared.GlobalData;
import com.example.dynamic_forms.client.GreetingServiceAsync;
import com.example.dynamic_forms.client.User;
import com.example.dynamic_forms.client.GreetingService;
import com.example.dynamic_forms.shared.FieldVerifier;
import com.google.gwt.json.client.JSONObject;
import com.google.gwt.rpc.client.RpcService;
import com.google.gwt.user.client.rpc.AsyncCallback;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

import java.lang.StringBuilder;



/**
 * The server side implementation of the RPC service.
 */
@SuppressWarnings("serial")
public class GreetingServiceImpl extends RemoteServiceServlet implements
GreetingService {

	//Database Connection Variables ////
	private Connection conn = null, conn1 = null;
	private String status;
	private String url  = "jdbc:mysql://localhost/alif_data"; 
	private String url_form = "jdbc:mysql://localhost/forms_metadata"; 
	private String user = "root";
	private String pass = "shibani";

	private GreetingServiceAsync rpctoClient;

	// Other Variables

	/*public GreetingServiceImpl() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, user, pass);

		      System.out.println("Connected to database: ");
		      System.out.println("username ->"+GlobalData.username);
		      authenticateUser(user,pass);
					/*PreparedStatement ps = conn.prepareStatement("select * from user ");
					ResultSet result = ps.executeQuery();
					while (result.next()) {

						System.out.println(result.getString(1));
						System.out.println(result.getString(2));
					}
					result.close();
					ps.close();*/

	/*	      conn.close();   
		}catch (SQLException e) {
			System.err.println("Mysql Connection Error: "+conn);
			e.printStackTrace();
			//NEVER catch exceptions like this
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}*/

	// Authenticating the user logging in /// 
	public String authenticateUser(String user1, String pass1) {
		//User user2 = new User();
		StringBuilder sb = new StringBuilder();
		String ans = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, user, pass);

			System.out.println("Connected to database: ");

			PreparedStatement ps = conn.prepareStatement("select * from user where Username = \"" + user1 + "\" AND " +
					"Password = \"" + pass1 + "\""
			);
			ResultSet result = ps.executeQuery();

			while (result.next()) {
				//user2 = new User(result.getString(1),
				//	result.getString(2));
				sb.append("flag:login;");
				sb.append("username:");
				sb.append(result.getString(1));
				sb.append(",password:");
				sb.append(result.getString(2));
				sb.append(",");
				ans=sb.toString();
				System.out.println(result.getString(1));
				System.out.println(result.getString(2));
			}


			result.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			System.err.println("Mysql Connection Error: "+conn);
			e.printStackTrace();
			//NEVER catch exceptions like this
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		//return user2;
		return ans;
	}

	////////// Authentication Ends here /////////////////////////////

	// Not getting called from DynamicForms.java.... (Don't know the reason)

	public void authenticateUser(String user1, String pass1, AsyncCallback<User> callback)
	{
		try {
			PreparedStatement ps = conn.prepareStatement("select * from user where Username = \"" + user1 + "\" AND " +
					"Password = \"" + pass1 + "\""
			);
			ResultSet result = ps.executeQuery();
			while (result.next()) {

				System.out.println(result.getString(1));
				System.out.println(result.getString(2));
			}
			result.close();
			ps.close();
		} catch (SQLException sqle) {
			//do stuff on fail
		}
		//return null;
	}


	public User insertUser(String uname,String passwd, String sec_q, String sec_ans){
		User user2 = new User();
		String query = "insert into user (Username,Password,Security_Q,Security_Ans) values ('"+uname+"','"+passwd+"','"+sec_q+"','"+sec_ans+"');";

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, user, pass);

			Statement update = conn.createStatement();
			update.executeUpdate(query);

			//get last id 
			ResultSet result = update.getGeneratedKeys(); 
			if (result != null && result.next()) { 
				int rsId = result.getInt(1); 
				System.out.println("resid ->"+rsId);
			}
			else
				System.out.println("result set is null");

			result.close();
			update.close();
			conn.close();

		} catch (SQLException sqle) {

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user2;


	}

	@Override
	public String getBasicInfo(String eid) {
		StringBuilder sb = new StringBuilder();
		String ans = null;
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, user, pass);

			System.out.println("Connected to database: ");

			PreparedStatement ps = conn.prepareStatement("select * from basicinfo where EMPI = \"" + eid + "\""
			);
			ResultSet result = ps.executeQuery();

			while (result.next()) {
				//user2 = new User(result.getString(1),
				//	result.getString(2));
				sb.append("flag:basicinfo;");
				sb.append("chart_ID:");
				sb.append(result.getString(2));
				sb.append(",EMPID:");
				sb.append(result.getString(3));
				sb.append(",last_name:");
				sb.append(result.getString(4));
				sb.append(",first_name:");
				sb.append(result.getString(5));
				sb.append(",Gender:");
				sb.append(result.getString(6));
				sb.append(",DOB:");
				sb.append(result.getString(7));
				sb.append(",admission_date:");
				sb.append(result.getString(8));	
				sb.append(",race_ethnicity:");
				sb.append(result.getString(9));
				sb.append(",height:");
				sb.append(result.getString(10));
				sb.append(",weight:");
				sb.append(result.getString(11));
				sb.append(",nicotine:");
				sb.append(result.getString(12));
				sb.append(",pending_litigation:");
				sb.append(result.getString(13));
				sb.append(",workers_compensation_claim:");
				sb.append(result.getString(14));	
				sb.append(",");
				ans=sb.toString();
				//System.out.println(result.getString(1));
				//System.out.println(result.getString(2));
			}


			result.close();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			System.err.println("Mysql Connection Error: "+conn);
			e.printStackTrace();
			//NEVER catch exceptions like this
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		//return user2;
		return ans;
	}


	public String selectOperation(String EMPI , String procDate){

		StringBuilder sbOper = new StringBuilder();
		String ans = null;

		connectToDB();

		try {
			PreparedStatement ps = conn1.prepareStatement("Select Procedure_ID from operation where EMPI = '"+EMPI+"' and Procedure_Date = '"+procDate+"'");

			ResultSet result = ps.executeQuery();
			while (result.next()){
				sbOper.append("flag:Operation;");
				//sbOper.append("Proc_ID:");
				sbOper.append(result.getString(1));
				ans = sbOper.toString();
				System.out.println(result.getString(1));
			}
			result.close();
			ps.close();
			conn1.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}



	public String insertPatient(String chartID , String EMPI, String Lname, String Fname, String Gender, String DOB, String AdmnDate , String Race, int ht, int wt, String nicotine, String PendLitGn , String wrkrComp, String UserName){ 

		User user2 = new User();
		String ans=null;
		// Connecting to the database
		connectToDB();

		String insPatQuery = "Insert into basicinfo(Chart_ID,EMPI,Lname,Fname, Gender, Date_Of_Birth, Admission_Date, Race_Ethinicity, Height, Weight, Nicotine, Pending_Litigation, Workers_Compensation_Claim, Username) values ('"+chartID+"', '"+EMPI+"', '"+Lname+"', '"+Fname+"', '"+Gender+"', '"+DOB+"', '"+AdmnDate+"', '"+Race+"', '"+ht+"', '"+wt+"', '"+nicotine+"', '"+PendLitGn+"', '"+wrkrComp+"', '"+UserName+"')";
		//System.out.println(insPatQuery);
		try {
			Statement update = conn1.createStatement();
			update.executeUpdate(insPatQuery);

			ResultSet result = update.getGeneratedKeys(); 
			if (result != null && result.next()) { 
				int rsId = result.getInt(1); 
				System.out.println("resid ->"+rsId);
			}
			else
				System.out.println("result set is null");

			ans="PatientDataAdded";
			result.close();
			update.close();
			conn1.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		//return user2;
		return ans;
	}

	public String insertOperation(String EMPI, String procType, String surgeon, String procDate, String cutTime, String closeTime, int EBL , String intraOPComp, String complicn, String procNotes, String username){

		User user2 = new User();
		String ans=null;

		connectToDB();

		String insOpnQuery = "Insert into Operation (EMPI, Procedure_Type, Surgeon, Procedure_Date, Cut_Time, Close_Time, EBL, IntraOP_Complication, Complication, Procedure_Notes, Username) values ('"+EMPI+"', '"+procType+"', '"+surgeon+"', '"+procDate+"', '"+cutTime+"', '"+closeTime+"', '"+EBL+"', '"+intraOPComp+"', '"+complicn+"', '"+procNotes+"', '"+username+"')";

		try {
			Statement update = conn1.createStatement();
			update.executeUpdate(insOpnQuery);

			ResultSet result = update.getGeneratedKeys(); 
			if (result != null && result.next()) { 
				int rsId = result.getInt(1); 
				System.out.println("resid ->"+rsId);
			}
			else
				System.out.println("result set is null");

			ans="InsertedOperData";
			result.close();
			update.close();
			conn1.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		//return user2;
		return ans;



	}

	public String insertOpnDetail (String procID , String cageType , int cageHt, int cageLordosis, String BMP, String plateType, String screwType, String level, String username ){
		User user2 = new User();
		String ans=null;
		connectToDB();

		String insOpnDetQuery = "Insert into operation_detail (Proc_ID, Cage_Type, Cage_Height, Cage_Lordosis, BMP, Plate_Type, Screw_Type, Level, Username) values ('"+procID+"', '"+cageType+"', '"+cageHt+"', '"+cageLordosis+"', '"+BMP+"', '"+plateType+"', '"+screwType+"', '"+level+"', '"+username+"')";

		try {
			Statement update = conn1.createStatement();
			update.executeUpdate(insOpnDetQuery);

			ResultSet result = update.getGeneratedKeys(); 
			if (result != null && result.next()) { 
				int rsId = result.getInt(1); 
				System.out.println("resid ->"+rsId);
			}
			else
				System.out.println("result set is null");

			ans="ProcDetailsAdded";
			result.close();
			update.close();
			conn1.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		return ans;
	}

	// select query to get the procId from Operation table



	public void connectToDB(){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			conn1 = DriverManager.getConnection(url, user, pass);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	public void connectToFormDB(){
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			conn = DriverManager.getConnection(url_form, user, pass);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	@Override
	public String insertImageData(String image_date, String image_type,
			int body_part, String EMPI, String username) {
		// TODO Auto-generated method stub
		User user2 = new User();
		String ans = null;
		connectToDB();

		String insimageDateQuery = "Insert into imaging (Image_Date, Image_Type, Body_Part, Username, EMPI) values ('"+image_date+"', '"+image_type+"', '"+body_part+"', '"+username+"', '"+EMPI+"')";

		try {
			Statement update = conn1.createStatement();
			update.executeUpdate(insimageDateQuery);

			ResultSet result = update.getGeneratedKeys(); 
			if (result != null && result.next()) { 
				int rsId = result.getInt(1); 
				System.out.println("resid ->"+rsId);
			}
			else
				System.out.println("result set is null");

			ans="DataofImageInserted";
			result.close();
			update.close();
			conn1.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		//return user2;
		return ans;
	}

	@Override
	public String insertImageInterpretationData(int image_ID,
			int operation_detail_ID, String image_months, String overall_fusion,
			int overall_score, String username) {
		// TODO Auto-generated method stub
		User user2 = new User();
		String ans = null;
		connectToDB();

		String insimageInterDateQuery = "Insert into imaging_interpretation (Operation_Detail_Id, Image_Id, Image_Months, Overall_Score, Overall_Fusion, Username) values ('"+operation_detail_ID+"', '"+image_ID+"', '"+image_months+"', '"+overall_score+"', '"+overall_fusion+"', '"+username+"')";

		try {
			Statement update = conn1.createStatement();
			update.executeUpdate(insimageInterDateQuery);

			ResultSet result = update.getGeneratedKeys(); 
			if (result != null && result.next()) { 
				int rsId = result.getInt(1); 
				System.out.println("resid ->"+rsId);
			}
			else
				System.out.println("result set is null");

			result.close();
			update.close();
			conn1.close();

			ans="InterpretationData";

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		//return user2;
		return ans;
	}

	@Override
	public String insertImageView(int image_ID, String classification, String username) {
		// TODO Auto-generated method stub
		User user2 = new User();
		String ans=null;
		connectToDB();

		String insimageViewQuery = "Insert into imaging_views (Image_ID, View/Classification, Username) values ('"+image_ID+"', '"+classification+"', '"+username+"')";

		try {
			Statement update = conn1.createStatement();
			update.executeUpdate(insimageViewQuery);

			ResultSet result = update.getGeneratedKeys(); 
			if (result != null && result.next()) { 
				int rsId = result.getInt(1); 
				System.out.println("resid ->"+rsId);
			}
			else
				System.out.println("result set is null");

			ans="ImageViewData";
			result.close();
			update.close();
			conn1.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		return ans;
	}

	@Override
	public String selectImage(String EMPI, String image_date) {
		// TODO Auto-generated method stub
		StringBuilder sbImage = new StringBuilder();
		String ans = null;

		connectToDB();

		try {
			PreparedStatement ps = conn1.prepareStatement("Select Image_ID from imaging where EMPI = '"+EMPI+"' and Image_Date = '"+image_date+"'");

			ResultSet result = ps.executeQuery();
			while (result.next()){
				sbImage.append("flag:Imaging;");
				//sbOper.append("Proc_ID:");
				sbImage.append(result.getString(1));
				ans = sbImage.toString();
				System.out.println(result.getString(1));
			}
			result.close();
			ps.close();
			conn1.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public String selectOperationDetail(String EMPI, String username) {
		// TODO Auto-generated method stub
		StringBuilder sbOperDetail = new StringBuilder();
		String ans = null;

		connectToDB();

		try {
			PreparedStatement ps = conn1.prepareStatement("Select Operation_Detail_Id from operation_detail where EMPI = '"+EMPI+"' and Username = '"+username+"'");

			ResultSet result = ps.executeQuery();
			while (result.next()){
				sbOperDetail.append("flag:OperationDetail;");
				//sbOper.append("Proc_ID:");
				sbOperDetail.append(result.getString(1));
				ans = sbOperDetail.toString();
				System.out.println(result.getString(1));
			}
			result.close();
			ps.close();
			conn1.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public String selectImageInterID(int operation_detail_ID, int image_ID) {
		// TODO Auto-generated method stub
		StringBuilder sbImageInterDetail = new StringBuilder();
		String ans = null;

		connectToDB();

		try {
			PreparedStatement ps = conn1.prepareStatement("Select Interpretation_Id from imaging_interpretation where Operation_Detail_Id = '"+operation_detail_ID+"' and Image_Id = '"+image_ID+"'");

			ResultSet result = ps.executeQuery();
			while (result.next()){
				sbImageInterDetail.append("flag:ImageInterpretation;");
				//sbOper.append("Proc_ID:");
				sbImageInterDetail.append(result.getString(1));
				ans = sbImageInterDetail.toString();
				System.out.println(result.getString(1));
			}
			result.close();
			ps.close();
			conn1.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public String insertReadsData(int interpretation_ID, String fusion, int score,
			String left, String right, String center, String reader, String Username) {
		// TODO Auto-generated method stub
		User user2 = new User();
		String ans=null;
		// Connecting to the database
		connectToDB();

		String insReadsQuery = "Insert into reads(Interpretation_ID,Fusion,Score,Left, Center, Right, Reader, Username) values ('"+interpretation_ID+"', '"+fusion+"', '"+score+"', '"+left+"', '"+center+"', '"+right+"', '"+reader+"', '"+Username+"')";
		//System.out.println(insPatQuery);
		try {
			Statement update = conn1.createStatement();
			update.executeUpdate(insReadsQuery);

			ResultSet result = update.getGeneratedKeys(); 
			if (result != null && result.next()) { 
				int rsId = result.getInt(1); 
				System.out.println("resid ->"+rsId);
			}
			else
				System.out.println("result set is null");

			ans="ReadsDataInserted";
			result.close();
			update.close();
			conn1.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return ans;
	}

	@Override
	public String insertForm(String form_name, String date_created, String creator, String form_modified) {
		// TODO Auto-generated method stub
		User user2 = new User();
		String ans = null;
		connectToFormDB();

		String insformDataQuery = "Insert into form (FormName, DateCreated, CreatedBy, Tobedisplayed, form_modified) values ('"+form_name+"', '"+date_created+"', '"+creator+"', 1, '"+form_modified+"')";

		try {
			PreparedStatement ps = conn.prepareStatement("Select FormID from form where FormName = '"+form_name+"'");

			ResultSet result = ps.executeQuery();

			result.last();
			int size = result.getRow();
			if(size>0)
			{
				System.out.println("result"+result.getString(1));
				System.out.println("form name ->"+form_name);
				System.out.println("form name already exists");
				ps.close();
				result.close();
			}
			else
			{
				System.out.println("form name does not exists.... hence adding");
				Statement update = conn.createStatement();
				update.executeUpdate(insformDataQuery);

				/*	ResultSet result = update.getGeneratedKeys(); 
		if (result != null && result.next()) { 
			int rsId = result.getInt(1); 
			System.out.println("resid ->"+rsId);
		}
		else
			System.out.println("result set is null");
				 */
				ps.close();
				result.close();
				update.close();
			}
			conn.close();

			ans="formAdded";

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		return ans;
	}

	@Override
	public String selectFormId(String form_name) {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		String ans = null;

		connectToFormDB();

		try {
			PreparedStatement ps = conn.prepareStatement("Select FormID from form where FormName = '"+form_name+"'");

			ResultSet result = ps.executeQuery();
			while (result.next()){
				sbOper.append("flag:SelectedId;");
				//sbOper.append("Proc_ID:");
				sbOper.append(result.getString(1));
				ans = sbOper.toString();
				System.out.println(result.getString(1));
			}
			result.close();
			ps.close();
			conn.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}


	// new attri getting added in the form db as well as creating or altering misc table in alif data db
	@Override
	public String insertFormField(int form_id, int formfield_id, String question, String form_name, String datatype, String defaultVal, int multiVal, String flg, String reftbl, String refattri) {
		// TODO Auto-generated method stub
		User user2 = new User();
		String ans=null;
		int dv;
		int sr_num = 0;
		int chkflg=0;
		int alreadyexistsflg=0;

		connectToDB();
		connectToFormDB();

		if(datatype.equals("String")||datatype.equals("string"))
		{
			datatype = "VARCHAR(100)";
		}

		if(datatype.equals("Integer") || datatype.equals("integer") || datatype.equals("int") || datatype.equals("Int"))
		{
			datatype = "INT";
		}

		String insformfieldDataQuery = "Insert into formfield (FormID, FormFieldID, Question_Name) values ('"+form_id+"', '"+formfield_id+"', '"+question+"')";
		String insaddtable = "Insert into table_names (Table_name) values ('"+form_name+"_misc')";
		String insaddtablemulti = "Insert into table_names (Table_name) values ('"+form_name+"_"+question+"')";
		String insaddtablenewform = "Insert into table_names (Table_name) values ('"+form_name+"')";


		String createtbl;
		String altertbl;
		String insaddattri;
		String createtblnewform;


		/* ************* HERE INSTEAD OF ADDING TO MISC TABLE ADD COL TO ORIGINAL TABLE  ***************** */

		//if(nullVal==1)
		//	{
		if(datatype.equals("Integer") || datatype.equals("integer") || datatype.equals("int") || datatype.equals("Int"))
		{
			dv = Integer.parseInt(defaultVal);
			if(reftbl.contains("NA"))
			{
				altertbl = "Alter table "+form_name+" ADD "+question+" "+datatype+" NULL Default '"+dv+"'";
				//	altertbl = "Alter table "+form_name+"_misc Add "+question+" "+datatype+" NULL Default '"+dv+"'";
				createtbl = "Create table "+form_name+"_"+question+" (EMPI varchar(45),"+question+" "+datatype+" NULL Default '"+dv+"' ,foreign key (EMPI) references basicinfo(EMPI))";
				createtblnewform = "Create table "+form_name+" (EMPI varchar(45),"+question+" "+datatype+" NULL Default '"+dv+"' ,foreign key (EMPI) references basicinfo(EMPI))";
			}
			else
			{
				altertbl = "Alter table "+form_name+" ADD ("+question+" "+datatype+" NULL Default '"+dv+"' , FOREIGN KEY ("+refattri+") REFERENCES "+reftbl+"("+refattri+"))";
				//	altertbl = "Alter table "+form_name+"_misc Add "+question+" "+datatype+" NULL Default '"+dv+"'";
				createtbl = "Create table "+form_name+"_"+question+" (EMPI varchar(45),"+question+" "+datatype+" NULL Default '"+dv+"' ,foreign key (EMPI) references basicinfo(EMPI) ,foreign key ("+refattri+") references "+reftbl+"("+refattri+"))";
				createtblnewform = "Create table "+form_name+" (EMPI varchar(45),"+question+" "+datatype+" NULL Default '"+dv+"' ,foreign key (EMPI) references basicinfo(EMPI) ,foreign key ("+refattri+") references "+reftbl+"("+refattri+"))";				
			}
		}
		else
		{
			if(reftbl.contains("NA"))
			{
				altertbl = "Alter table "+form_name+" ADD "+question+" "+datatype+" NULL Default '"+defaultVal+"'";
				//	altertbl = "Alter table "+form_name+"_misc Add "+question+" "+datatype+" NULL Default '"+defaultVal+"'";
				createtbl = "Create table "+form_name+"_"+question+" (EMPI varchar(45),"+question+" "+datatype+" NULL Default '"+defaultVal+"' ,foreign key (EMPI) references basicinfo(EMPI))";
				createtblnewform = "Create table "+form_name+" (EMPI varchar(45),"+question+" "+datatype+" NULL Default '"+defaultVal+"' ,foreign key (EMPI) references basicinfo(EMPI))";
			}
			else
			{
				altertbl = "Alter table "+form_name+" ADD ("+question+" "+datatype+" NULL Default '"+defaultVal+"' , FOREIGN KEY ("+refattri+") REFERENCES "+reftbl+"("+refattri+"))";
				//	altertbl = "Alter table "+form_name+"_misc Add "+question+" "+datatype+" NULL Default '"+defaultVal+"'";
				createtbl = "Create table "+form_name+"_"+question+" (EMPI varchar(45),"+question+" "+datatype+" NULL Default '"+defaultVal+"' ,foreign key (EMPI) references basicinfo(EMPI) ,foreign key ("+refattri+") references "+reftbl+"("+refattri+"))";
				createtblnewform = "Create table "+form_name+" (EMPI varchar(45),"+question+" "+datatype+" NULL Default '"+defaultVal+"' ,foreign key (EMPI) references basicinfo(EMPI) ,foreign key ("+refattri+") references "+reftbl+"("+refattri+"))";

			}
		}   

		System.out.println("form name ->"+form_name);

		try {
			// FOR MULTI VALUE ATTRI 
			if(multiVal==1)
			{
				//insert row into table_names table in form_metadata db with the multi value attri table name
				Statement update = conn.createStatement();
				update.executeUpdate(insaddtablemulti);
				update.close(); 
			}

			//FOR NEW FORM
			if(flg.contains("1"))
			{
				PreparedStatement ps = conn.prepareStatement("Select Table_name from table_names where Table_name = '"+form_name+"'");

				ResultSet result = ps.executeQuery();
				System.out.println("result before while ->"+result);

				int cnt=0;


				while(result.next())
				{
					cnt++;
				}
				System.out.println("count ->"+cnt);

				if(cnt==0)
				{
					System.out.println("result set empty!");

					System.out.println("result null");
					result.close();
					ps.close();

					//insert row into table_names table in form_metadata db with the new form table name
					Statement update = conn.createStatement();
					update.executeUpdate(insaddtablenewform);
					update.close(); 
				}
				else
				{
					chkflg=1;
				}
				
				// select the table id and insert into form_table the form_id and table_id
				PreparedStatement ps2 = conn.prepareStatement("Select Sr_num from table_names where Table_name = '"+form_name+"'");
				ResultSet result2 = ps2.executeQuery();

				while(result2.next())
				{
					String insertfidtid = "Insert into form_table (Form_ID, Table_ID) values ('"+form_id+"' , '"+result2.getInt(1)+"')";
					Statement update2 = conn.createStatement();
					update2.executeUpdate(insertfidtid);
					update2.close();
				}
				
				result2.close();
				ps2.close();
			}


			PreparedStatement ps1;
			//FOR MULTI VAL ATTRI
			if(multiVal==1)
				ps1 = conn.prepareStatement("Select Sr_num from table_names where Table_name = '"+form_name+"_"+question+"'");
			else if(flg.contains("1"))
				ps1 = conn.prepareStatement("Select Sr_num from table_names where Table_name = '"+form_name+"'");
			else
				ps1 = conn.prepareStatement("Select Sr_num from table_names where Table_name = '"+form_name+"'");

			ResultSet result1 = ps1.executeQuery();
			while (result1.next()){
				//System.out.println(result1.getString(1));
				sr_num = Integer.parseInt(result1.getString(1));
				int c=0;
				//first check if attribute for tat table already exists
				//if not then insert into attribute_names table and set flg.
				//only if flag set then alter table and add col below
				PreparedStatement ps_test_attr = conn.prepareStatement("Select Attri_name from attribute_names where Sr_num = '"+sr_num+"' AND Attri_name ='"+question+"'");
				ResultSet result_test_attr = ps_test_attr.executeQuery();
				
				while(result_test_attr.next())
				{
					c++;
				}
				System.out.println("c ->"+c+" sr_num ->"+sr_num);
				if(c==0)
				{
					insaddattri = "Insert into attribute_names(Sr_num , Attri_name , Primary_key , Foreign_key , Datatype) values ('"+sr_num+"' , '"+question+"' , '0' , '0' ,'"+datatype+"')";
					Statement update2 = conn.createStatement();
					update2.executeUpdate(insaddattri);
					update2.close();
					alreadyexistsflg =1;
				}
				else
					alreadyexistsflg=0;
			}
			result1.close();
			ps1.close();

			/* **************************************************************************** */	    	
			System.out.println("result not null");
			if(multiVal==1)
			{
				Statement update1 = conn1.createStatement();
				update1.executeUpdate(createtbl);
				update1.close();
			}
			else if(flg.contains("1") && chkflg==0)
			{
				Statement update1 = conn1.createStatement();
				update1.executeUpdate(createtblnewform);
				update1.close();
			}
			else
			{
				////// alter table with new col only if attribute does not already exists as a col in the table
				// this is checked above before inserting in attribute_names table.
				if(alreadyexistsflg==1)
				{
					System.out.println("inside if of already exists flag");
					Statement update1 = conn1.createStatement();
					update1.executeUpdate(altertbl);
					update1.close();
					System.out.println("if done");
				}
				
			}
			//}  
			System.out.println("form id ->"+form_id+" formfieldid ->"+formfield_id+" question ->"+question);
			Statement update = conn.createStatement();
			update.executeUpdate(insformfieldDataQuery);


			update.close();
			conn.close();
			conn1.close();
			ans="fieldsInserted";

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		//return user2;
		return ans;
	}

	@Override
	public String selectModifiedFormId(String form_modified) {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		String ans = null;

		connectToFormDB();

		try {
			PreparedStatement ps = conn.prepareStatement("Select FormID from form where FormName = '"+form_modified+"'");

			ResultSet result = ps.executeQuery();
			while (result.next()){
				sbOper.append("flag:FormModifiedId;");
				//sbOper.append("Proc_ID:");
				sbOper.append(result.getString(1));
				ans = sbOper.toString();
				System.out.println(result.getString(1));
			}
			result.close();
			ps.close();
			conn.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public String selectFormFieldSno(int form_id, int formfield_id, String question) {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		String ans = null;

		connectToFormDB();

		try {
			//		PreparedStatement ps = conn.prepareStatement("Select FormFieldSno from formfield where FormID = '"+form_id+"' and FormFieldID = '"+formfield_id+"' and Question_Name = '"+question+"'");
			PreparedStatement ps = conn.prepareStatement("Select FormFieldSno from formfield where FormID = '"+form_id+"' and Question_Name = '"+question+"'");

			ResultSet result = ps.executeQuery();
			if(result == null)
			{
				System.out.println("result null");
			}
			while (result.next()){
				sbOper.append("flag:FormFieldSno;");
				//sbOper.append("Proc_ID:");
				sbOper.append(result.getString(1));
				ans = sbOper.toString();
				System.out.println(result.getString(1));
			}
			result.close();
			ps.close();
			conn.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public User insertFormFieldAddorDel(int form_id, int formfield_srno_add,
			int form_modified_id) {
		// TODO Auto-generated method stub
		User user2 = new User();

		connectToFormDB();

		String insformfieldAddorDelDataQuery = "Insert into formfieldaddordel (FormID, FormFieldIDAdded, FormID_of_ModifiedForm) values ('"+form_id+"', '"+formfield_srno_add+"', '"+form_modified_id+"')";

		try {
			Statement update = conn.createStatement();
			update.executeUpdate(insformfieldAddorDelDataQuery);

			/*	ResultSet result = update.getGeneratedKeys(); 
		if (result != null && result.next()) { 
			int rsId = result.getInt(1); 
			System.out.println("resid ->"+rsId);
		}
		else
			System.out.println("result set is null");

		result.close();*/
			update.close();
			conn.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		return user2;
	}

	@Override
	public User insertOptions(int formfield_srno, String option) {
		// TODO Auto-generated method stub
		User user2 = new User();

		connectToFormDB();

		String insoptionsDataQuery = "Insert into formfieldoption (FormFieldSno, OptionText) values ('"+formfield_srno+"', '"+option+"')";

		try {
			Statement update = conn.createStatement();
			update.executeUpdate(insoptionsDataQuery);

			/*	ResultSet result = update.getGeneratedKeys(); 
		if (result != null && result.next()) { 
			int rsId = result.getInt(1); 
			System.out.println("resid ->"+rsId);
		}
		else
			System.out.println("result set is null");

		result.close();*/
			update.close();
			conn.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 

		return user2;

	}
	
	
	//below function will be used to insert data into the db ...... first finding all table names related to the form ->
	// then finding all attri for each table -> if any col matches the col data to be added then ->
	// selecting the table name and inserting/updating data
	
	@Override
	public User insertData(String formnm, String EMPI, String[] col, String[] val) {
		// TODO Auto-generated method stub
		User user2 = new User();

		// Connecting to the database
		connectToFormDB();
		connectToDB();

		int tbl_id;
		String attri_nm,tbl_nm;
		int form_id = 0;
		
		//need to get form_id as parameter instead of table name 
		//get all table names for this form using form_table table...... get attributes for each table ...... compare each attri with col name and then
		// insert/update data to the correct table

		//System.out.println("connection fdb ->"+conn);
		//System.out.println("connection db ->"+conn1);
		
		System.out.println("col length ->"+col.length);
		System.out.println("form_id ->"+formnm);

		try {
			PreparedStatement p = conn.prepareStatement("Select FormID from form where FormName = '"+formnm+"'");

			ResultSet r = p.executeQuery();

			while(r.next())
			{
				form_id = r.getInt(1);
				System.out.println("form_id ->"+form_id);
			}
			
			r.close();
			p.close();

			
			for(int i=0;i<col.length;i++)
			{
				System.out.println("col[i] ->"+col[i]);
				System.out.println("val[i] ->"+val[i]);
				PreparedStatement ps2 = conn.prepareStatement("Select Table_ID from form_table where Form_ID = '"+form_id+"'");

				ResultSet result2 = ps2.executeQuery();

				while(result2.next())
				{
					tbl_id = result2.getInt(1);
					PreparedStatement ps3 = conn.prepareStatement("Select Attri_name from attribute_names where Sr_num = '"+tbl_id+"'");

					ResultSet result3 = ps3.executeQuery();

					while(result3.next())
					{
						attri_nm = result3.getString(1);
						if(col[i].equals(attri_nm))
						{
							PreparedStatement ps4 = conn.prepareStatement("Select Table_name from table_names where Sr_num = '"+tbl_id+"'");

							ResultSet result4 = ps4.executeQuery();

							while(result4.next())
							{
								tbl_nm=result4.getString(1);
								//insert or update attri_nm into result4
								String insPatmiscQuery = "Insert into "+tbl_nm+"(EMPI,"+attri_nm+") values ('"+EMPI+"', '"+val[i]+"')";
								String updateQuery = "Update "+tbl_nm+" set "+attri_nm+" = '"+val[i]+"' where EMPI = '"+EMPI+"'";
								//System.out.println(insPatQuery);
								//ResultSet result = null;
								//Statement update = null;
								//try {

								PreparedStatement ps = conn1.prepareStatement("Select EMPI from "+tbl_nm+" where EMPI = '"+EMPI+"'");

								ResultSet result1 = ps.executeQuery();

								int cnt=0;

								while(result1.next())
								{
									cnt++;
								}
								System.out.println("count ->"+cnt);

								if(cnt==0)
								{
									System.out.println("result set empty!");
									
									//insert new row
									Statement update = conn1.createStatement();
									update.executeUpdate(insPatmiscQuery);
									/*ResultSet rs = update.getGeneratedKeys(); 
									
									if (rs != null && rs.next()) { 
										int rsId = rs.getInt(1); 
										System.out.println("resid ->"+rsId);
									}
									else
										System.out.println("result set is null");
								
									rs.close();*/
									update.close();
								}
								else
								{
									//update table
									
									Statement update7 = conn1.createStatement();
									update7.executeUpdate(updateQuery);

									/*ResultSet rs1 = update7.getGeneratedKeys(); 
									
									if (rs1 != null && rs1.next()) { 
										int rsId = rs1.getInt(1); 
										System.out.println("resid ->"+rsId);
									}
									else
										System.out.println("result set is null");
								
									rs1.close(); */
									update7.close();
								}

								result1.close();
								ps.close();
								//result.close();
								//update.close();
								
								if(i==col.length-1)
									break;
							}

							result4.close();
							ps4.close();
							
							if(i==col.length-1)
								break;

						}
					}

					result3.close();
					ps3.close();
					
					if(i==col.length-1)
						break;

				}

				result2.close();
				ps2.close();
				
				

			}
			conn.close();
			conn1.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return user2;
	}
	
	
	
	

	@Override
	public User insertMiscData(String table_name, String EMPI, String col, String val) {
		// TODO Auto-generated method stub
		User user2 = new User();
		// Connecting to the database
		connectToDB();
		
		String insPatmiscQuery = "Insert into "+table_name +"(EMPI,"+col+") values ('"+EMPI+"', '"+val+"')";
		String updateQuery = "Update "+table_name+" set "+col+" = '"+val+"' where EMPI = '"+EMPI+"'";
		//System.out.println(insPatQuery);
		ResultSet result = null;
		Statement update = null;
		try {

			PreparedStatement ps = conn.prepareStatement("Select EMPI from "+table_name+" where EMPI = '"+EMPI+"'");

			ResultSet result1 = ps.executeQuery();

			int cnt=0;

			while(result1.next())
			{
				cnt++;
			}
			System.out.println("count ->"+cnt);

			if(cnt==0)
			{
				System.out.println("result set empty!");

				//insert new row
				update = conn1.createStatement();
				update.executeUpdate(insPatmiscQuery);

				result = update.getGeneratedKeys(); 
				if (result != null && result.next()) { 
					int rsId = result.getInt(1); 
					System.out.println("resid ->"+rsId);
				}
				else
					System.out.println("result set is null");
			}
			else
			{
				//update table
				update = conn1.createStatement();
				update.executeUpdate(updateQuery);

				result = update.getGeneratedKeys(); 
				if (result != null && result.next()) { 
					int rsId = result.getInt(1); 
					System.out.println("resid ->"+rsId);
				}
				else
					System.out.println("result set is null");
			}

			result1.close();
			ps.close();
			result.close();
			update.close();
			conn1.close();



		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return user2;
	}

	@Override
	public ArrayList<String> selectTableNames() {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		ArrayList<String> ans = new ArrayList<String>();

		connectToFormDB();

		try {

			PreparedStatement ps = conn.prepareStatement("Select Table_name from table_names");

			ResultSet result = ps.executeQuery();

			while (result.next()){
				ans.add(result.getString(1));
			}
			result.close();
			ps.close();
			conn.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting Table Names");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		System.out.println("table names ->"+ans);
		return ans;
	}
	@Override
	public ArrayList<String> selectAttributeNames(String table_name) {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		ArrayList<String> ans = new ArrayList<String>();

		connectToFormDB();

		try {
			PreparedStatement ps_prep = conn.prepareStatement("Select Sr_num from table_names where Table_name = '"+table_name+"'");
			ResultSet result_prep = ps_prep.executeQuery();
			int Sr_num = 0;
			if (result_prep.next()){
				Sr_num = result_prep.getInt(1);
			}

			PreparedStatement ps = conn.prepareStatement("Select Attri_name from attribute_names where Sr_num = '"+Sr_num+"'");
			ResultSet result = ps.executeQuery();
			while (result.next()){
				sbOper.append("flag:SelectedId;");
				ans.add(result.getString(1));
			}
			result.close();
			ps.close();
			conn.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting Attribute Names");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public String getFormId(String username, String form_name) {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		String ans = null;
		PreparedStatement ps = null, ps1=null;

		connectToFormDB();

		try {
			/*if(form_name.contains("BasicInfo"))
			 ps = conn.prepareStatement("Select FormID from form where CreatedBy = '"+username+"' and FormName like 'BasicInfo_%'");
		else if(form_name.contains("Imaging"))
			 ps = conn.prepareStatement("Select FormID from form where CreatedBy = '"+username+"' and FormName like 'Imaging_%'");
			 
			if(form_name.contains("ImagingAnalysis"))
				ps = conn.prepareStatement("Select * from form where CreatedBy = '"+username+"' and FormName like 'ImagingAnalysis_%'");
			else if(form_name.contains("Operation"))
			 ps = conn.prepareStatement("Select FormID from form where CreatedBy = '"+username+"' and FormName like 'Operation_%'");
			 
			else if(form_name.contains("ProcedureDetail"))
				ps = conn.prepareStatement("Select * from form where CreatedBy = '"+username+"' and FormName like 'ProcedureDetail_%'");
			else*/
				ps = conn.prepareStatement("Select FormID,form_modified from form where FormName = '"+form_name+"'");





			ResultSet result = ps.executeQuery();
			ResultSet r = null;
			String res = new String();
			String mod_form = new String();
			String mod_form_id = new String();
			int cnt=0;

			while(result.next())
			{
				res=result.getString(1);
				mod_form = result.getString(2);
				System.out.println("modified form name ->"+mod_form);
				if(!mod_form.contains("NA"))
				{
					ps1 = conn.prepareStatement("Select FormID from form where FormName = '"+mod_form+"'");
					r = ps1.executeQuery();
					while(r.next())
					{
						mod_form_id = r.getString(1);
						System.out.println("mod form id ->"+mod_form_id);
					}
					r.close();
					ps1.close();
				}
				else
				{
					mod_form_id = "-1";
				}
					cnt++;
			}

			if(cnt==0)
			{
				sbOper.append("flag:Form_Id;");
				//sbOper.append("Proc_ID:");
				sbOper.append("0,-1");
				ans = sbOper.toString();

			}
			else{
				sbOper.append("flag:Form_Id;");
				//sbOper.append("Proc_ID:");
				sbOper.append(res);
				sbOper.append(",");
				sbOper.append(mod_form_id);
				ans = sbOper.toString();
				System.out.println(ans);

			}
			
			
			result.close();
			ps.close();
			conn.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public String getFormfieldDetails(int form_id, int mod_form_id) {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		String ans = null;
		int cnt=0;
		connectToFormDB();
		
		System.out.println("form_id ->"+form_id);

		sbOper.append("flag:Form_field;");
		try {
			if(mod_form_id > -1)
			{
				PreparedStatement ps = conn.prepareStatement("select * from formfield where FormID = '"+mod_form_id+"'");

				ResultSet result = ps.executeQuery();
				
				while(result.next()){
					System.out.println("inside result");
					sbOper.append("cnt:");
					sbOper.append(cnt);
					sbOper.append(",Formfieldtype:");
					if(result.getString(3).equals("1"))
						sbOper.append("TextBox");
					else if(result.getString(3).equals("2"))
						sbOper.append("RadioButton");
					else if(result.getString(3).equals("3"))
						sbOper.append("CheckBox");
					else if(result.getString(3).equals("4"))
						sbOper.append("Dropdown");
					else if(result.getString(3).equals("5"))
						sbOper.append("Button");
					sbOper.append(",Question:");
					sbOper.append(result.getString(4));

					if(result.getString(3).equals("2")||result.getString(3).equals("3")||result.getString(3).equals("4"))
					{
						PreparedStatement ps1 = conn.prepareStatement("select * from formfieldoption where FormFieldSno = '"+result.getString(1)+"'");
						ResultSet result1 = ps1.executeQuery();
						String temp = "";

						sbOper.append(",Options:");
						while(result1.next())
						{
							System.out.println("inside result 1");
							temp = temp + result1.getString(2) + "-";
						}
						sbOper.append(temp);
						sbOper.append(",");
					}
					else
						sbOper.append(",");
					cnt++;

				}

				ans = sbOper.toString();
				System.out.println(ans);

				result.close();
				ps.close();
			}
			
			PreparedStatement ps = conn.prepareStatement("select * from formfield where FormID = '"+form_id+"'");

			ResultSet result = ps.executeQuery();
			//sbOper.append("flag:Form_field;");
			while(result.next()){
				System.out.println("inside result");
				sbOper.append("cnt:");
				sbOper.append(cnt);
				sbOper.append(",Formfieldtype:");
				if(result.getString(3).equals("1"))
					sbOper.append("TextBox");
				else if(result.getString(3).equals("2"))
					sbOper.append("RadioButton");
				else if(result.getString(3).equals("3"))
					sbOper.append("CheckBox");
				else if(result.getString(3).equals("4"))
					sbOper.append("Dropdown");
				else if(result.getString(3).equals("5"))
					sbOper.append("Button");
				sbOper.append(",Question:");
				sbOper.append(result.getString(4));

				if(result.getString(3).equals("2")||result.getString(3).equals("3")||result.getString(3).equals("4"))
				{
					PreparedStatement ps1 = conn.prepareStatement("select * from formfieldoption where FormFieldSno = '"+result.getString(1)+"'");
					ResultSet result1 = ps1.executeQuery();
					String temp = "";

					sbOper.append(",Options:");
					while(result1.next())
					{
						System.out.println("inside result 1");
						temp = temp + result1.getString(2) + "-";
					}
					sbOper.append(temp);
					sbOper.append(",");
				}
				else
					sbOper.append(",");
				cnt++;

			}

			ans = sbOper.toString();
			System.out.println(ans);

			result.close();
			ps.close();
			conn.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
		
	}





	// To get a list of all new forms created.

	@Override
	public String getFormNames() {
		// TODO Auto-generated method stub
		connectToFormDB();
		String ans = new String();
		StringBuilder sbOper = new StringBuilder();

		try {
		//	PreparedStatement ps = conn.prepareStatement("select * from form where FormID > 7");

			PreparedStatement ps = conn.prepareStatement("select * from form where Tobedisplayed = 1");
			
			ResultSet result = ps.executeQuery();
			sbOper.append("FormNames;");
			while(result.next()){
				sbOper.append(result.getString(2));
				sbOper.append(",");
			}

			ans = sbOper.toString();
			System.out.println(ans);

			result.close();
			ps.close();
			conn.close();

		}catch(SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public String getPatNames() {
		// TODO Auto-generated method stub
		connectToDB();
		String ans = new String();
		String fname = new String();
		String lname = new String();
		String name= new String();
		StringBuilder sbOper = new StringBuilder();

		try {
			PreparedStatement ps = conn1.prepareStatement("select * from basicinfo");

			ResultSet result = ps.executeQuery();
			sbOper.append("PatNames;");
			while(result.next()){
				fname = result.getString(4);
				lname = result.getString(3);
				name = fname+" "+lname;
				System.out.println("pat name ->"+name);
				sbOper.append(name);
				sbOper.append(",");
			}

			ans = sbOper.toString();
			System.out.println(ans);

			result.close();
			ps.close();
			conn1.close();

		}catch(SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public String getEMPI(String patname) {
		// TODO Auto-generated method stub
		connectToDB();
		String ans = new String();
		String fname = new String();
		String lname = new String();

		String [] name = patname.split(" ");
		fname = name[0];
		lname = name[1];
		StringBuilder sbOper = new StringBuilder();

		try {
			PreparedStatement ps = conn1.prepareStatement("select * from basicinfo where First_Name = '"+fname+"' AND Last_Name = '"+lname+"'");

			ResultSet result = ps.executeQuery();
			sbOper.append("flag:EMPI;");
			while(result.next()){
				sbOper.append(result.getString(2));		
				System.out.println("EMPI ->"+result.getString(2));				
			}

			ans = sbOper.toString();
			System.out.println(ans);

			result.close();
			ps.close();
			conn1.close();

		}catch(SQLException e) {
			System.err.println("Error in Selecting from Operation");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		return ans;
	}

	@Override
	public String selectprimkey(String tbl_nm) {
		// TODO Auto-generated method stub
		String ans= new String();

		connectToFormDB();

		try {

			PreparedStatement ps = conn.prepareStatement("Select Attri_name from attribute_names where Primary_key = 1 AND Sr_num in (Select Sr_num from table_names where Table_name = '"+tbl_nm+"')");


			ResultSet result = ps.executeQuery();

			while (result.next()){
				ans = result.getString(1);
				System.out.println("answer ->"+ans);
			}
			result.close();
			ps.close();
			conn.close();



		}catch (SQLException e) {
			System.err.println("Error in Selecting Table Names");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}

		if(ans == null)
			ans = "not present";

		return ans;
	}

	//function which updates the table defination for new tables created like adding prim key, foreign key etc.
	@Override
	public User updateTabledefination(String tbl_name, String cardinality,
			String uniqueattrinm, String reltable, String relattri,
			String relationshipName) {
		// TODO Auto-generated method stub

		String prim_key = new String();
		String datatype = new String();
		String datatype_newtbl = new String();
		connectToFormDB();

		String altertbldef = "Alter table "+tbl_name+" ADD CONSTRAINT prim_key PRIMARY KEY ("+uniqueattrinm+")";
		String alterreltbldef = "Alter table "+reltable+" ADD CONSTRAINT foreign_key FOREIGN KEY ("+uniqueattrinm+") REFERENCES "+tbl_name+" ("+uniqueattrinm+") ";
		String updateprimval = "Update attribute_names set Primary_key = 1 where Attri_name = '"+uniqueattrinm+"' AND Sr_num in (Select Sr_num from table_names where Table_name = '"+tbl_name+"')";
		
		try {
			Statement update1 = conn.createStatement();
			update1.executeUpdate(altertbldef);
			update1.close(); 
			
			Statement update2 = conn.createStatement();
			update2.executeUpdate(updateprimval);
			update2.close();
			
			if((reltable!=null) && (cardinality.contains("1:1") || cardinality.contains("1:N")))
			{
				Statement update = conn.createStatement();
				update.executeUpdate(alterreltbldef);
				update.close();
			}
			
			if(cardinality.contains("M:N"))
			{
				PreparedStatement ps = conn.prepareStatement("Select * from attribute_names where Primary_key = 1 AND Sr_num in (Select Sr_num from table_names where Table_name = '"+reltable+"')");
				ResultSet result = ps.executeQuery();

				while (result.next()){
					prim_key = result.getString(2);
					datatype = result.getString(5);
					System.out.println("primary key of other table in M:N relationship  ->"+prim_key);
				}
				result.close();
				ps.close();
				
				PreparedStatement ps1 = conn.prepareStatement("Select * from attribute_names where Primary_key = 1 AND Sr_num in (Select Sr_num from table_names where Table_name = '"+tbl_name+"')");
				ResultSet result1 = ps1.executeQuery();

				while (result1.next()){
					datatype_newtbl = result1.getString(5);
					System.out.println("primary key of other table in M:N relationship  ->"+datatype_newtbl);
				}
				result1.close();
				ps1.close();
				
				String createtbl = "Create table "+relationshipName+" ("+uniqueattrinm+" "+datatype_newtbl+", "+prim_key+" "+datatype+", FOREIGN KEY ("+uniqueattrinm+") REFERENCES "+tbl_name+"("+uniqueattrinm+"), FOREIGN KEY ("+prim_key+") REFERENCES "+reltable+"("+prim_key+"))";
				Statement update3 = conn.createStatement();
				update3.executeUpdate(createtbl);
				update3.close();
				
			}
			
			conn.close();
		}
		catch(SQLException e) {
			System.err.println("Error in Selecting Table Names");
			e.printStackTrace();			
		}
		return null;
	}

	
	public ArrayList<ArrayList> getPKFK(ArrayList<String> selectedTbl) {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		ArrayList ans = new ArrayList();
		connectToFormDB();

		try {
			//get PK
			ArrayList<String> pkList = new ArrayList<String>();
			pkList.add("");
			for(int i = 0; i < selectedTbl.size(); i++){
				String table_name = selectedTbl.get(i);
				PreparedStatement ps_prep = conn.prepareStatement("Select Sr_num from table_names where Table_name = '"+table_name+"'");
				ResultSet result_prep = ps_prep.executeQuery();
				int Sr_num = 0;
				if (result_prep.next()){
					Sr_num = result_prep.getInt(1);
				}
	
				PreparedStatement ps = conn.prepareStatement("Select Attri_name from attribute_names where Sr_num = '"+Sr_num+"' and Primary_key = '1'");
				ResultSet result = ps.executeQuery();
				while (result.next()){
					sbOper.append("flag:SelectedId;");
					pkList.add(result.getString(1));
				}
				result.close();
				ps.close();
			}
			ans.add(pkList);

			//get FK
			for(int i = 0; i < selectedTbl.size(); i++){
				ArrayList<String> fkList = new ArrayList<String>();
				String table_name2 = selectedTbl.get(i);
				PreparedStatement ps_prep2 = conn.prepareStatement("Select Sr_num from table_names where Table_name = '" + table_name2 + "'");
				ResultSet result_prep2 = ps_prep2.executeQuery();
				int Sr_num2 = 0;
				if (result_prep2.next()){
					Sr_num2 = result_prep2.getInt(1);
				}

				PreparedStatement ps2 = conn.prepareStatement("Select Attri_name from attribute_names where Sr_num = '"+Sr_num2+"' and Foreign_key = '1'");
				ResultSet result2 = ps2.executeQuery();
				while (result2.next()){
					sbOper.append("flag:SelectedId;");
					fkList.add(result2.getString(1));
				}
			
				result2.close();
				ps2.close();
				ans.add(fkList);
			}
			
			conn.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting Attribute Names");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}
		return ans;
	}
	
	
	public ArrayList<ArrayList<String>> getQueryResult(String Query) {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		ResultSet result = null;
		ArrayList<ArrayList<String>> ans = new ArrayList<ArrayList<String>>();
		connectToDB();

		try {
			PreparedStatement ps = conn1.prepareStatement(Query);
			result = ps.executeQuery();
			ResultSetMetaData meta = result.getMetaData();
			int colCount = meta.getColumnCount();
			
			ArrayList<String> colName = new ArrayList<String>();
			
			for(int i = 1; i <= colCount; i++){
				colName.add(meta.getColumnName(i));	
			}
			
			ans.add(colName);
			
			while (result.next()){
				ArrayList<String> col = new ArrayList<String>();

				for(int i = 1; i <= colCount; i++){
					col.add(result.getString(i));
				}
				ans.add(col);
			}
			result.close();
			ps.close();
			conn1.close();

		}catch (SQLException e) {
			System.err.println("Error in Querying");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}
		return ans;
	}
	
	
	public User saveQuery(String uname, String queryToSave, String DBname){
		User user2 = new User();
		String query = "insert into savedqueries (Username,Query,Description) values ('"+uname+"','"+queryToSave+"','"+DBname+"');";

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url_form, user, pass);

			Statement update = conn.createStatement();
			update.executeUpdate(query);

			//get last id 
			ResultSet result = update.getGeneratedKeys(); 
			if (result != null && result.next()) { 
				int rsId = result.getInt(1); 
				System.out.println("resid ->"+rsId);
			}
			else
				System.out.println("result set is null");

			result.close();
			update.close();
			conn.close();

		} catch (SQLException sqle) {

		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user2;
	}
	
	public ArrayList<ArrayList> getKeys(ArrayList<ArrayList<String>> selectedTbls) {
		// TODO Auto-generated method stub
		StringBuilder sbOper = new StringBuilder();
		ArrayList SelKeys = new ArrayList();
		ArrayList OpKeys = new ArrayList();
		ArrayList<ArrayList> out = new ArrayList<ArrayList>();

		
		connectToFormDB();

		try {
			ArrayList selectedTbl = selectedTbls.get(0);
			ArrayList selectedTblOp = selectedTbls.get(1);
			
			//get Keys
			for(int i = 0; i < selectedTbl.size(); i++){
				ArrayList<String> keyList = new ArrayList<String>();
				String table_name2 = (String) selectedTbl.get(i);
				PreparedStatement ps_prep2 = conn.prepareStatement("Select Sr_num from table_names where Table_name = '" + table_name2 + "'");
				ResultSet result_prep2 = ps_prep2.executeQuery();
				int Sr_num2 = 0;
				if (result_prep2.next()){
					Sr_num2 = result_prep2.getInt(1);
				}

				PreparedStatement ps2 = conn.prepareStatement("Select Attri_name from attribute_names where Sr_num = '"+Sr_num2+"' and (Foreign_key = '1' or Primary_key = '1')");
				ResultSet result2 = ps2.executeQuery();
				while (result2.next()){
					sbOper.append("flag:SelectedId;");
					keyList.add(result2.getString(1));
				}
			
				result2.close();
				ps2.close();
				SelKeys.add(keyList);
			}
			
			for(int i = 0; i < selectedTblOp.size(); i++){
				ArrayList<String> keyList = new ArrayList<String>();
				String table_name2 = (String) selectedTblOp.get(i);
				PreparedStatement ps_prep2 = conn.prepareStatement("Select Sr_num from table_names where Table_name = '" + table_name2 + "'");
				ResultSet result_prep2 = ps_prep2.executeQuery();
				int Sr_num2 = 0;
				if (result_prep2.next()){
					Sr_num2 = result_prep2.getInt(1);
				}

				PreparedStatement ps2 = conn.prepareStatement("Select Attri_name from attribute_names where Sr_num = '"+Sr_num2+"' and (Foreign_key = '1' or Primary_key = '1')");
				ResultSet result2 = ps2.executeQuery();
				while (result2.next()){
					sbOper.append("flag:SelectedId;");
					keyList.add(result2.getString(1));
				}
			
				result2.close();
				ps2.close();
				OpKeys.add(keyList);
			}
			
			out.add(SelKeys);
			out.add(OpKeys);
			conn.close();

		}catch (SQLException e) {
			System.err.println("Error in Selecting Attribute Names");
			e.printStackTrace();
			//NEVER catch exceptions like this
		}
		return out;
	}
}
